﻿Imports System.Data.SqlClient
Imports CrystalDecisions.CrystalReports.Engine

Public Class frmProfitandLoss

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Me.ErrProv.Clear()

        If Me.RBranch.Checked = True And Me.CombBranch.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombBranch, "Please select a valid branch from the list")
        Else
            If Me.RIncomeStatement.Checked = True Then
                ShowIncomeStatement()

            ElseIf Me.RIncomeStatementDetails.Checked = True Then
                ShowIncomeStatementDetails()

            ElseIf Me.RRevExpGrowth.Checked = True Then
                ShowRevenuesVsExpenses()

            ElseIf Me.RBranchComparison.Checked = True Then
                ShowBranchesComparison()

            End If
        End If
    End Sub

    Sub ShowIncomeStatement()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim CondBranch As String

            If Me.RBranch.Checked = True Then
                CondBranch = " and Branch=N'" & Me.CombBranch.Text & "' "
            Else
                CondBranch = ""
            End If

            Dim dap As New SqlDataAdapter("Select Acc1,Acc2,Acc3,Sum(TotalIn)-Sum(TotalOut) TotalIn " & _
                                          "From Transactions Where TransDate>=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & _
                                          "and Acc1 In (N'Expenses',N'Revenues') " & CondBranch & " Group By Acc1,Acc2,Acc3", cnn)
            Dim das As New DataSet
            das.Clear()

            cnn.Open()
            dap.Fill(das, "Transactions")
            cnn.Close()

            Dim rpt As New IncomeStatement
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranch.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowIncomeStatementDetails()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim CondBranch As String

            If Me.RBranch.Checked = True Then
                CondBranch = " and Branch=N'" & Me.CombBranch.Text & "' "
            Else
                CondBranch = ""
            End If

            Dim dap As New SqlDataAdapter("Select Month(TransDate) SNo,Acc1,Acc2," & _
                                          "TotalIn-TotalOut TotalIn From Transactions Where " & _
                                          "TransDate>=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & _
                                          "and Acc1 In (N'Expenses',N'Revenues') " & CondBranch, cnn)
            Dim das As New DataSet
            das.Clear()

            cnn.Open()
            dap.Fill(das, "Transactions")
            cnn.Close()

            Dim rpt As New IncomeStatement12Months
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranch.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowRevenuesVsExpenses()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim CondBranch As String

            If Me.RBranch.Checked = True Then
                CondBranch = " and Branch=N'" & Me.CombBranch.Text & "' "
            Else
                CondBranch = ""
            End If

            Dim dap As New SqlDataAdapter("Select Acc1,Month(TransDate) SNo," & _
                                          "Case When Sum(TotalIn)-Sum(TotalOut)<0 Then Sum(TotalOut)-Sum(TotalIn) " & _
                                          "       Else Sum(TotalIn)-Sum(TotalOut) End  TotalIn From Transactions Where " & _
                                          "TransDate>=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & _
                                          "and Acc1 In (N'Expenses',N'Revenues') " & CondBranch & " Group By Acc1,Month(TransDate)", cnn)
            Dim das As New DataSet

            cnn.Open()
            dap.Fill(das, "Transactions")
            cnn.Close()

            Dim rpt As New RevenuesVsExpenses
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            'Branch PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("Branch")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            If Me.RBranch.Checked = True Then
                crParameterDiscreteValue.Value = Me.CombBranch.Text
            Else
                crParameterDiscreteValue.Value = "ALL"
            End If
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub ShowBranchesComparison()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim dap As New SqlDataAdapter("Select Branch,Acc1,Acc2,Sum(TotalIn)-Sum(TotalOut) TotalIn From Transactions Where " & _
                                          "TransDate>=N'" & Me.DTPPeriodFRm.Value.ToShortDateString & " 00:00:00' " & _
                                          "and TransDate<=N'" & Me.DTPPeriodTo.Value.ToShortDateString & " 23:59:59' " & _
                                          "and Acc1 In (N'Expenses',N'Revenues') Group By Branch,Acc1,Acc2", cnn)
            Dim das As New DataSet

            cnn.Open()
            dap.Fill(das, "Transactions")
            cnn.Close()

            Dim rpt As New BranchesComparison
            rpt.SetDataSource(das)

            'Create Parameters
            Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue
            Dim crParameterFieldDefinitions As ParameterFieldDefinitions
            Dim crParameterFieldLocation As ParameterFieldDefinition
            Dim crParameterValues As New CrystalDecisions.Shared.ParameterValues
            crParameterFieldDefinitions = rpt.DataDefinition.ParameterFields

            'MinDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MinDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodFRm.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)


            'MaxDate PARAMETER
            crParameterFieldLocation = crParameterFieldDefinitions.Item("MaxDate")
            crParameterValues = crParameterFieldLocation.CurrentValues
            crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
            crParameterDiscreteValue.Value = Me.DTPPeriodTo.Value
            crParameterValues.Add(crParameterDiscreteValue)
            crParameterFieldLocation.ApplyCurrentValues(crParameterValues)

            Me.CrystalReportViewer1.ReportSource = rpt

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RBranch_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RBranch.CheckedChanged
        If Me.RBranch.Checked = True Then
            Me.CombBranch.Enabled = True

        ElseIf Me.RAll.Checked = True Then
            Me.CombBranch.Enabled = False

        End If
    End Sub

    Private Sub RBranchComparison_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RBranchComparison.CheckedChanged
        If Me.RBranchComparison.Checked = True Then
            Me.RBranch.Enabled = False
            Me.CombBranch.Enabled = False
            Me.RAll.Enabled = True
            Me.RAll.Checked = True

        Else
            Me.RBranch.Enabled = True
        End If
    End Sub

    Sub FillBranches()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Branches As ArrayList = GetBranches()

            For Each Branch As String In Branches
                Me.CombBranch.Items.Add(Branch)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmProfitandLoss_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillBranches()
    End Sub
End Class